package com.oceanread.cn.controller;


import com.oceanread.cn.config.MyConfig;
import com.oceanread.cn.config.Result;
import com.oceanread.cn.entity.*;
import com.oceanread.cn.service.*;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.tomcat.util.http.fileupload.util.Streams;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;

@RestController
@RequestMapping("/file")
@CrossOrigin()
public class FileController {
    @Autowired
    private IProjectionsService iProjectionsService;
    @Autowired
    private IPipelineService iPipelineService;
    @Autowired
    private ITurbinelistService iTurbinelistService;
    @Autowired
    private ITurbineService iTurbineService;
    @Autowired
    private IDrillhole1Service iDrillhole1Service;
    @Autowired
    private IDrillhole2Service iDrillhole2Service;
    @Autowired
    private IProfileService iProfileService;
    @Autowired
    private ISedimentService iSedimentService;
    @Autowired
    private ISideScanService iSideScanService;
    @Autowired
    private IWaterDepthService iWaterDepthService;
    private static final Logger logger = LoggerFactory.getLogger(FileController.class);
    //在文件操作中，不用/或者\最好，推荐使用File.separator
    private final static String fileDir = "excelfiles";
    private final static String rootPath = System.getProperty("user.home") + File.separator + fileDir + File.separator;

    /**
     * 上传Excel文件
     *
     * @param fileType
     * @param multipartFiles
     * @param response
     * @param request
     * @return
     */
    @PostMapping("/uploadExcel/{fileType}")
    public Object uploadFile(@PathVariable("fileType") String fileType, @RequestParam("file") MultipartFile[] multipartFiles,
                             final HttpServletResponse response, final HttpServletRequest request) {
        Result result;
        File fileDir = new File(rootPath);
        if (!fileDir.exists() && !fileDir.isDirectory()) {
            fileDir.mkdirs();
        }
        try {
            if (multipartFiles != null && multipartFiles.length > 0) {
                for (int i = 0; i < multipartFiles.length; i++) {
                    MultipartFile file = multipartFiles[i];
                    String fileName = multipartFiles[i].getOriginalFilename();
                    String filetype = fileName.substring(fileName.lastIndexOf("."));
                    if (filetype.equals(".xls") || filetype.equals(".xlsx"))//处理xls文件
                    {
                        try {
                            //以原来的名称命名,覆盖掉旧的
                            String storagePath = rootPath + multipartFiles[i].getOriginalFilename();
                            logger.info("上传的文件：" + multipartFiles[i].getName() + "," + multipartFiles[i].getContentType() + "," + multipartFiles[i].getOriginalFilename()
                                    + "，保存的路径为：" + storagePath);
                            /*保存文件*/
                            Streams.copy(multipartFiles[i].getInputStream(), new FileOutputStream(storagePath), true);
                            System.out.println(storagePath);
                            /*处理文件*/
                            result = readExcel(fileType, storagePath);
                            System.out.println("完成批量上传 " + fileType + " 内容");
                            /*删除下载的文件*/
                            File filedelete = new File(storagePath);
                            if (filedelete.exists() && filedelete.isFile()) {
                                if (filedelete.delete()) {
                                    System.out.println("删除单个文件" + fileName + "成功！");
                                } else {
                                    System.out.println("删除单个文件" + fileName + "失败！");
                                }
                            }
                            return result;
                        } catch (IOException e) {
                            logger.error(e.getMessage());
                            return Result.error(e.getMessage());
                        }
                    }
                }
            }
        } catch (Exception e) {
            return Result.error(e.getMessage());
        }
        return Result.error();
    }

    private Result readExcel(String fileType, String filePath) {
        Result result = new Result();
        if ("Projections".equals(fileType)) {
            /*项目总表*/
            result = dealwithProjections(filePath);
        } else if ("Pipeline".equals(fileType)) {
            /*海底管线*/
            result = dealwithPipeline(filePath);
        } else if ("Faninfo".equals(fileType)) {
            /*海上风机 Faninfo */
            result = dealwithTurbine(filePath);
        } else if ("Drillhole".equals(fileType)) {
            /*钻孔*/
            result = dealwithDrillhole(filePath);
        } else if ("Profile".equals(fileType)) {
            /*浅剖*/
            result = dealwithProfile(filePath);
        } else if ("Sediment".equals(fileType)) {
            /*底质*/
            result = dealwithSediment(filePath);
        } else if ("SideScan".equals(fileType)) {
            /*侧扫*/
            result = dealwithSideScan(filePath);
        } else if ("WaterDepth".equals(fileType)) {
            /*水深*/
            result = dealwithWaterDepth(filePath);
        } else {
            System.out.println("sss");
        }
        return result;
    }

    /*处理插入Projections类型文件       项目列表*/
    private Result dealwithProjections(String filePath) {
        try {
            InputStream inputStream = new FileInputStream(new File(filePath));
            Workbook hssfWorkbook = null;
            if (filePath.endsWith("xlsx")) {
                hssfWorkbook = new XSSFWorkbook(inputStream);//Excel 2007
            } else if (filePath.endsWith("xls")) {
                hssfWorkbook = new HSSFWorkbook(inputStream);//Excel 2003
            }
            List<Projections> projections = new ArrayList<Projections>();
            //  循环工作表
            for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
                Sheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
                if (hssfSheet == null) {
                    continue;
                }
                if (numSheet == 0) {  //只插入第一个表
                    // 循环行Row
                    for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                        Row hssRow = hssfSheet.getRow(rowNum);
                        if (hssRow != null) {
                            try {
                                Projections projection = new Projections();
                                Cell PrjCode = hssRow.getCell(0);//项目编码
                                projection.setPrjCode(String.valueOf(PrjCode));
                                Cell prjName = hssRow.getCell(1);//项目名称
                                projection.setName(String.valueOf(prjName));
                                Cell ShortName = hssRow.getCell(2);//项目简称
                                projection.setShortName(String.valueOf(ShortName));
                                Cell Date = hssRow.getCell(3);//测量时间
                                projection.setDate(String.valueOf(Date));
                                Cell Lon = hssRow.getCell(4);//经度
                                projection.setLon(String.valueOf(Lon));
                                Cell Lat = hssRow.getCell(5);//纬度
                                projection.setLat(String.valueOf(Lat));
                                Cell CameraHight = hssRow.getCell(6);//相机高度
                                projection.setCameraHight(String.valueOf(CameraHight));
                                Cell Type = hssRow.getCell(7);//项目类型
                                projection.setType(String.valueOf(Type));
                                Cell NumPipeline = hssRow.getCell(8);//管线数量
                                projection.setNumPipeline(String.valueOf(NumPipeline));
                                Cell LengthPipeline = hssRow.getCell(9);//管线长度(Km)
                                projection.setLengthPipeline(String.valueOf(LengthPipeline));
                                Cell DepthPipeline = hssRow.getCell(10);//管线埋深(m)
                                projection.setDepthPipeline(String.valueOf(DepthPipeline));
                                Cell NumDrillhole = hssRow.getCell(11);//钻孔数量
                                projection.setType(String.valueOf(NumDrillhole));
                                Cell NumProfile = hssRow.getCell(12);//浅剖数量
                                projection.setType(String.valueOf(NumProfile));
                                Cell UndertakingUnit = hssRow.getCell(13);//承担单位
                                projection.setType(String.valueOf(UndertakingUnit));
                                if (PrjCode != null && String.valueOf(PrjCode) != ""
                                        && String.valueOf(PrjCode) != "null") {
                                    projections.add(projection);
                                }
                            } catch (Exception e) {
                                logger.error(String.valueOf(e));
                            }
                        }
                    }

                    for (Projections projection : projections) {
                        iProjectionsService.insert(
                                projection.getPrjCode(), projection.getName(),
                                projection.getShortName(), projection.getDate(),
                                projection.getLon(), projection.getLat(),projection.getType(),
                                projection.getCameraHight(), projection.getNumPipeline(),
                                projection.getLengthPipeline(), projection.getDepthPipeline(),
                                projection.getNumDrillhole(),projection.getNumProfile(),
                                projection.getUndertakingUnit()
                        );
                    }
                }
            }
            return Result.success();

        } catch (IOException e) {
            e.printStackTrace();
            return Result.error("1", e.getMessage());
        }
    }

    /*处理插入Pipeline 类型文件     管线*/
    private Result dealwithPipeline(String filePath) {
        try {
            InputStream inputStream = new FileInputStream(new File(filePath));
            Workbook hssfWorkbook = null;
            if (filePath.endsWith("xlsx")) {
                hssfWorkbook = new XSSFWorkbook(inputStream);//Excel 2007
            } else if (filePath.endsWith("xls")) {
                hssfWorkbook = new HSSFWorkbook(inputStream);//Excel 2003
            }
            List<Pipeline> pipelines = new ArrayList<Pipeline>();
            //  循环工作表
            for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
                Sheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
                if (hssfSheet == null) {
                    continue;
                }
                if (numSheet == 0) {
                    // 循环行Row
                    for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                        Row hssRow = hssfSheet.getRow(rowNum);
                        if (hssRow != null) {
                            try {
                                Pipeline pipeline = new Pipeline();
                                Cell PrjCode = hssRow.getCell(0);//项目编码
                                pipeline.setPrjCode(String.valueOf(PrjCode));
                                Cell Name = hssRow.getCell(1);//项目名称
                                pipeline.setName(String.valueOf(Name));
                                Cell Type = hssRow.getCell(2);//管线类型
                                pipeline.setType(String.valueOf(Type));
                                Cell IDLine = hssRow.getCell(3); //管线编码
                                pipeline.setIDLine(String.valueOf(IDLine));
                                Cell Lon = hssRow.getCell(4);//经度
                                pipeline.setLon(String.valueOf(Lon));
                                Cell Lat = hssRow.getCell(5);//纬度
                                pipeline.setLat(String.valueOf(Lat));
                                Cell pointOrder = hssRow.getCell(6);//节点编号
                                try {
                                    int Line_int = (int) Double.parseDouble(pointOrder.toString());
                                    pipeline.setPointOrder(Line_int);
                                } catch (Exception e) {
                                    pipeline.setPointOrder(Integer.valueOf(pointOrder.toString()));
                                    System.out.println(e.getMessage());
                                }
                                Cell length = hssRow.getCell(7);//管线长度 (Km)
                                try {
                                    int length_int = (int) Double.parseDouble(length.toString());
                                    pipeline.setLength(String.valueOf(length_int));
                                } catch (Exception e) {
                                    pipeline.setLength(String.valueOf(length));
                                    System.out.println(e.getMessage());
                                }
                                Cell depth = hssRow.getCell(8);//管线深度 (m)
                                try {
                                    int depth_int = (int) Double.parseDouble(depth.toString());
                                    pipeline.setDepth(String.valueOf(depth_int));
                                } catch (Exception e) {
                                    pipeline.setDepth(String.valueOf(depth));
                                    System.out.println(e.getMessage());
                                }


                                if (PrjCode != null && String.valueOf(PrjCode) != ""
                                        && String.valueOf(PrjCode) != "null") {
                                    pipelines.add(pipeline);
                                }
                            } catch (Exception e) {
                                logger.error(String.valueOf(e));
                            }
                        }
                    }

                    for (Pipeline pipeline : pipelines) {
                        iPipelineService.insert(pipeline.getPrjCode(), pipeline.getName(),
                                pipeline.getType(), pipeline.getIDLine(), pipeline.getIDPoint(),
                                pipeline.getNorth(), pipeline.getEast(), pipeline.getLat(),
                                pipeline.getLon(), pipeline.getDistance(), pipeline.getDisZ(),
                                pipeline.getAngle(), pipeline.getNote(), pipeline.getLine(),
                               pipeline.getPointOrder(),pipeline.getLength(),pipeline.getDepth());
                    }
                }
            }
            return Result.success();

        } catch (IOException e) {
            e.printStackTrace();
            return Result.error("1", e.getMessage());
        }
    }

    /*处理插入Faninfo类型文件       海上风机Faninfo*/
    private Result dealwithTurbine(String filePath) {
        try {
            InputStream inputStream = new FileInputStream(new File(filePath));
            Workbook hssfWorkbook = null;
            if (filePath.endsWith("xlsx")) {
                hssfWorkbook = new XSSFWorkbook(inputStream);//Excel 2007
            } else if (filePath.endsWith("xls")) {
                hssfWorkbook = new HSSFWorkbook(inputStream);//Excel 2003
            } else {
                return Result.error();
            }
            List<Turbine> turbines = new ArrayList<Turbine>();
            List<Turbinelist> turbinelists = new ArrayList<Turbinelist>();
            //  循环工作表
            for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
                Sheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
                if (hssfSheet == null) {
                    continue;
                }
                if (numSheet == 0) {//风机项目列表
                    // 循环行Row
                    for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                        Row hssRow = hssfSheet.getRow(rowNum);
                        if (hssRow != null) {
                            try {
                                Turbinelist turbinelist = new Turbinelist();
                                Cell PrjCode = hssRow.getCell(0);//项目编码
                                try {
                                    int PrjCode_int = (int) Double.parseDouble(PrjCode.toString());
                                    turbinelist.setPrjCode(String.valueOf(PrjCode_int));
                                } catch (Exception e) {
                                    turbinelist.setPrjCode(String.valueOf(PrjCode));
                                    System.out.println(e.getMessage());
                                }
                                Cell Name = hssRow.getCell(1);//项目名称
                                turbinelist.setName(String.valueOf(Name));
                                Cell Region = hssRow.getCell(2);//地区
                                turbinelist.setName(String.valueOf(Region));
                                Cell Num = hssRow.getCell(4);//风机数量
                                turbinelist.setName(String.valueOf(Num));

                                Cell Datetime = hssRow.getCell(4); //工程日期
                                turbinelist.setName(String.valueOf(Datetime));
                                Cell Lon = hssRow.getCell(5);//视角经度
                                turbinelist.setName(String.valueOf(Lon));
                                Cell Lat = hssRow.getCell(6);//视角纬度
                                turbinelist.setName(String.valueOf(Lat));
                                Cell Height = hssRow.getCell(7);//相机高度
                                turbinelist.setName(String.valueOf(Height));

                                if (PrjCode != null && String.valueOf(PrjCode) != ""
                                        && String.valueOf(PrjCode) != "null") {
                                    turbinelists.add(turbinelist);
                                }
                            } catch (Exception e) {
                                logger.error(String.valueOf(e));
                            }
                        }
                    }

                    for (Turbinelist turbinelist : turbinelists) {
                        iTurbinelistService.insert(turbinelist.getPrjCode(), turbinelist.getName(),
                                turbinelist.getNum(),turbinelist.getRegion(),turbinelist.getDatetime(),
                                turbinelist.getLon(),turbinelist.getLat(),turbinelist.getHeight());
                    }
                }
                if (numSheet == 1) {
                    // 循环行Row
                    for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                        Row hssRow = hssfSheet.getRow(rowNum);
                        if (hssRow != null) {
                            try {
                                Turbine turbine = new Turbine();
                                Cell PrjCode = hssRow.getCell(0);//项目编码
                                try {
                                    int PrjCode_int = (int) Double.parseDouble(PrjCode.toString());
                                    turbine.setPrjCode(String.valueOf(PrjCode_int));
                                } catch (Exception e) {
                                    turbine.setPrjCode(String.valueOf(PrjCode));
                                    System.out.println(e.getMessage());
                                }
                                Cell Name = hssRow.getCell(1);//项目名称
                                turbine.setName(String.valueOf(Name));
                                Cell ID = hssRow.getCell(2);//风机编码
                                turbine.setID(String.valueOf(ID));
                                Cell Lon = hssRow.getCell(3);//经度
                                turbine.setLon(String.valueOf(Lon));
                                Cell Lat = hssRow.getCell(4);//纬度
                                turbine.setLat(String.valueOf(Lat));
                                Cell Status = hssRow.getCell(5);//风机状态
                                turbine.setStatus(String.valueOf(Status));
                                Cell Speed = hssRow.getCell(6);//风机转速(r/s)
                                try {
                                    int Speed_int = (int) Double.parseDouble(Speed.toString());
                                    turbine.setSpeed(String.valueOf(Speed_int));
                                } catch (Exception e) {
                                    turbine.setSpeed(String.valueOf(Speed));
                                }
                                Cell Power = hssRow.getCell(7);//风机功率(KW)
                                try {
                                    int Power_int = (int) Double.parseDouble(Power.toString());
                                    turbine.setPower(String.valueOf(Power_int));
                                } catch (Exception e) {
                                    turbine.setPower(String.valueOf(Power));
                                }
                                if (PrjCode != null && String.valueOf(PrjCode) != ""
                                        && String.valueOf(PrjCode) != "null") {
                                    turbines.add(turbine);
                                }
                            } catch (Exception e) {
                                logger.error(String.valueOf(e));
                            }
                        }
                    }

                    for (Turbine turbine : turbines) {
                        iTurbineService.insert(turbine.getPrjCode(), turbine.getName(),
                                turbine.getID(), turbine.getLat(),
                                turbine.getLon(), turbine.getStatus(),
                                turbine.getSpeed(), turbine.getPower());
                    }
                }

            }
            return Result.success();

        } catch (IOException e) {
            e.printStackTrace();
            return Result.error("1", e.getMessage());
        }
    }

    /*处理插入Drillhole类型文件       钻孔*/
    private Result dealwithDrillhole(String filePath) {
        try {
            InputStream inputStream = new FileInputStream(new File(filePath));
            Workbook hssfWorkbook = null;
            if (filePath.endsWith("xlsx")) {
                hssfWorkbook = new XSSFWorkbook(inputStream);//Excel 2007
            } else if (filePath.endsWith("xls")) {
                hssfWorkbook = new HSSFWorkbook(inputStream);//Excel 2003
            }
            List<Drillhole1> drillhole1s = new ArrayList<Drillhole1>();
            List<Drillhole2> drillhole2s = new ArrayList<Drillhole2>();
            //  循环工作表
            for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
                Sheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
                if (hssfSheet == null) {
                    continue;
                }
                if (numSheet == 0) {
                    /**
                     * 钻孔表1
                     */
                    // 循环行Row
                    for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                        Row hssRow = hssfSheet.getRow(rowNum);
                        if (hssRow != null) {
                            try {
                                Drillhole1 drillhole1 = new Drillhole1();
                                Cell PrjCode = hssRow.getCell(0);//项目编码
                                drillhole1.setPrjCode(String.valueOf(PrjCode));
                                Cell Name = hssRow.getCell(1);//项目名称
                                drillhole1.setName(String.valueOf(Name));
                                Cell PrjID = hssRow.getCell(2);//钻孔编号
                                drillhole1.setPrjID(String.valueOf(PrjID));
                                Cell Lat = hssRow.getCell(3);//纬度
                                drillhole1.setLat(String.valueOf(Lat));
                                Cell Lon = hssRow.getCell(4);//经度
                                drillhole1.setLon(String.valueOf(Lon));
                                Cell Sampling = hssRow.getCell(5);//采样员
                                drillhole1.setSampling(String.valueOf(Sampling));
                                Cell Responsible = hssRow.getCell(6);//负责人
                                drillhole1.setResponsible(String.valueOf(Responsible));
                                Cell Drawing = hssRow.getCell(7);//制图员
                                drillhole1.setDrawing(String.valueOf(Drawing));
                                Cell Proofreading = hssRow.getCell(8);//校对员
                                drillhole1.setProofreading(String.valueOf(Proofreading));
                                Cell Catalog = hssRow.getCell(9);//录入员
                                drillhole1.setCatalog(String.valueOf(Catalog));
                                Cell Altitude = hssRow.getCell(10);//地层编号
                                drillhole1.setAltitude(String.valueOf(Altitude));
                                Cell Start = hssRow.getCell(11);//起始深度
                                drillhole1.setStart(String.valueOf(Start));
                                Cell Thickness = hssRow.getCell(12);//地层厚度
                                drillhole1.setThickness(String.valueOf(Thickness));
                                Cell Material = hssRow.getCell(13);//地层材质
                                drillhole1.setMaterial(String.valueOf(Material));
                                Cell Material0 = hssRow.getCell(14);//材质描述
                                drillhole1.setMaterial0(String.valueOf(Material0));

                                if (PrjCode != null && String.valueOf(PrjCode) != ""
                                        && String.valueOf(PrjCode) != "null") {
                                    drillhole1s.add(drillhole1);
                                }
                            } catch (Exception e) {
                                logger.error(String.valueOf(e));
                            }
                        }
                    }

                    for (Drillhole1 drillhole1 : drillhole1s) {
                        iDrillhole1Service.insert(drillhole1.getPrjCode(), drillhole1.getCode(), drillhole1.getName(),
                                drillhole1.getPrjID(), drillhole1.getType(), drillhole1.getEquipment(), drillhole1.getLat(),
                                drillhole1.getLon(), drillhole1.getEast(), drillhole1.getNorth(), drillhole1.getSampling(),
                                drillhole1.getResponsible(), drillhole1.getDrawing(), drillhole1.getProofreading(),
                                drillhole1.getCatalog(), drillhole1.getTime(), drillhole1.getDiameter(), drillhole1.getAltitude(),
                                drillhole1.getStart(), drillhole1.getThickness(), drillhole1.getMaterial(),
                                drillhole1.getMaterial0()
                        );
                    }
                } else if (numSheet == 1) {
                    /**
                     * 钻孔表2
                     */
                    // 循环行Row
                    for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                        Row hssRow = hssfSheet.getRow(rowNum);
                        if (hssRow != null) {
                            try {
                                Drillhole2 drillhole2 = new Drillhole2();
                                Cell PrjCode = hssRow.getCell(0);//项目编码
                                drillhole2.setPrjCode(String.valueOf(PrjCode));
                                Cell PrjID = hssRow.getCell(1);//钻孔编号
                                drillhole2.setPrjID(String.valueOf(PrjID));
                                Cell Order = hssRow.getCell(2);//地层编号
                                drillhole2.setOrder(Integer.parseInt(String.valueOf(Order)));
                                Cell Start = hssRow.getCell(3);//起始深度
                                drillhole2.setStart(String.valueOf(Start));
                                Cell Thickness = hssRow.getCell(4);//地层厚度
                                drillhole2.setThickness(String.valueOf(Thickness));
                                Cell Material = hssRow.getCell(5);//地层材质
                                drillhole2.setMaterial(String.valueOf(Material));
                                Cell MaterialO = hssRow.getCell(6);//材质描述
                                drillhole2.setMaterial0(String.valueOf(MaterialO));

                                if (PrjCode != null && String.valueOf(PrjCode) != ""
                                        && String.valueOf(PrjCode) != "null") {
                                    drillhole2s.add(drillhole2);
                                }
                            } catch (Exception e) {
                                logger.error(String.valueOf(e));
                            }
                        }
                    }

                    for (Drillhole2 drillhole2 : drillhole2s) {
                        iDrillhole2Service.insert(drillhole2.getPrjCode(), drillhole2.getPrjID(), drillhole2.getOrder(),
                                drillhole2.getAltitude(), drillhole2.getStart(), drillhole2.getThickness(),
                                drillhole2.getMaterial(), drillhole2.getMaterial0()
                        );
                    }
                }

            }
            return Result.success();

        } catch (IOException e) {
            e.printStackTrace();
            return Result.error("1", e.getMessage());
        }
    }

    /*处理插入Profile类型文件       浅剖*/
    private Result dealwithProfile(String filePath) {
        try {
            InputStream inputStream = new FileInputStream(new File(filePath));
            Workbook hssfWorkbook = null;
            if (filePath.endsWith("xlsx")) {
                hssfWorkbook = new XSSFWorkbook(inputStream);//Excel 2007
            } else if (filePath.endsWith("xls")) {
                hssfWorkbook = new HSSFWorkbook(inputStream);//Excel 2003
            }
            List<Profile> profiles = new ArrayList<Profile>();
            //  循环工作表
            for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
                Sheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
                if (hssfSheet == null) {
                    continue;
                }
                if (numSheet == 0) {
                    // 循环行Row
                    for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                        Row hssRow = hssfSheet.getRow(rowNum);
                        if (hssRow != null) {
                            try {
                                Profile profile = new Profile();
                                Cell PrjCode = hssRow.getCell(0);//项目编码
                                profile.setPrjCode(String.valueOf(PrjCode));
                                Cell Name = hssRow.getCell(1);//项目名称
                                profile.setName(String.valueOf(Name));
                                Cell IDLine = hssRow.getCell(2);//测线编码
                                try {
                                    int Speed_int = (int) Double.parseDouble(IDLine.toString());
                                    profile.setIDline(String.valueOf(Speed_int));
                                } catch (Exception e) {
                                    profile.setIDline(String.valueOf(IDLine));
                                }
                                Cell Order = hssRow.getCell(3);//节点序号
                                try {
                                    int Speed_int = (int) Double.parseDouble(Order.toString());
                                    profile.setOrder(String.valueOf(Speed_int));
                                } catch (Exception e) {
                                    profile.setOrder(String.valueOf(Order));
                                }
                                Cell Lat = hssRow.getCell(4);//纬度
                                profile.setLat(String.valueOf(Lat));
                                Cell Lon = hssRow.getCell(5);//经度
                                profile.setLon(String.valueOf(Lon));
                                if (PrjCode != null && String.valueOf(PrjCode) != ""
                                        && String.valueOf(PrjCode) != "null") {
                                    System.out.println(PrjCode);
                                    profiles.add(profile);
                                }
                            } catch (Exception e) {
                                logger.error(String.valueOf(e));
                            }
                        }
                    }

                    for (Profile profile : profiles) {
                        iProfileService.insert(profile.getPrjCode(), profile.getCode(),
                                profile.getName(), profile.getIDline(), profile.getOrder(),
                                profile.getLat(), profile.getLon());
                    }
                }
            }
            return Result.success();

        } catch (IOException e) {
            e.printStackTrace();
            return Result.error("1", e.getMessage());
        }
    }

    /*处理插入Sediment类型文件     底质*/
    private Result dealwithSediment(String filePath) {
        try {
            InputStream inputStream = new FileInputStream(new File(filePath));
            Workbook hssfWorkbook = null;
            if (filePath.endsWith("xlsx")) {
                hssfWorkbook = new XSSFWorkbook(inputStream);//Excel 2007
            } else if (filePath.endsWith("xls")) {
                hssfWorkbook = new HSSFWorkbook(inputStream);//Excel 2003
            }
            List<Sediment> sediments = new ArrayList<Sediment>();
            //  循环工作表
            for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
                Sheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
                if (hssfSheet == null) {
                    continue;
                }
                if (numSheet == 0) {
                    // 循环行Row
                    for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                        Row hssRow = hssfSheet.getRow(rowNum);
                        if (hssRow != null) {
                            try {
                                Sediment sediment = new Sediment();
                                Cell PrjCode = hssRow.getCell(0);//项目编码
                                sediment.setPrjCode(String.valueOf(PrjCode));
                                Cell Name = hssRow.getCell(1);//项目名称
                                sediment.setName(String.valueOf(Name));
                                Cell url = hssRow.getCell(2);//存储路径
                                sediment.setUrl(String.valueOf(url));
                                if (PrjCode != null && String.valueOf(PrjCode) != ""
                                        && String.valueOf(PrjCode) != "null") {
                                    sediments.add(sediment);
                                }
                            } catch (Exception e) {
                                logger.error(String.valueOf(e));
                            }
                        }
                    }

                    for (Sediment sediment : sediments) {
                        iSedimentService.insert(sediment.getPrjCode(), sediment.getCode(),
                                sediment.getName(), sediment.getArea(), sediment.getTime(),
                                sediment.getCenterCoordinateX(), sediment.getCenterCoordinateY(), sediment.getCenterCoordinateZ(), sediment.getUrl());
                    }
                }
            }
            return Result.success();

        } catch (IOException e) {
            e.printStackTrace();
            return Result.error("1", e.getMessage());
        }
    }

    /*处理插入SideScan类型文件     侧扫*/
    private Result dealwithSideScan(String filePath) {
        try {
            InputStream inputStream = new FileInputStream(new File(filePath));
            Workbook hssfWorkbook = null;
            if (filePath.endsWith("xlsx")) {
                hssfWorkbook = new XSSFWorkbook(inputStream);//Excel 2007
            } else if (filePath.endsWith("xls")) {
                hssfWorkbook = new HSSFWorkbook(inputStream);//Excel 2003
            }
            List<SideScan> sideScans = new ArrayList<SideScan>();
            //  循环工作表
            for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
                Sheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
                if (hssfSheet == null) {
                    continue;
                }
                if (numSheet == 0) {
                    // 循环行Row
                    for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                        Row hssRow = hssfSheet.getRow(rowNum);
                        if (hssRow != null) {
                            try {
                                SideScan sideScan = new SideScan();
                                Cell PrjCode = hssRow.getCell(0);//项目编码
                                sideScan.setPrjCode(String.valueOf(PrjCode));
                                Cell Name = hssRow.getCell(1);//项目名称
                                sideScan.setName(String.valueOf(Name));
                                Cell url = hssRow.getCell(2);//存储路径
                                sideScan.setUrl(String.valueOf(url));

                                if (PrjCode != null && String.valueOf(PrjCode) != ""
                                        && String.valueOf(PrjCode) != "null") {
                                    sideScans.add(sideScan);
                                }
                            } catch (Exception e) {
                                logger.error(String.valueOf(e));
                            }
                        }
                    }

                    for (SideScan sideScan : sideScans) {
                        iSideScanService.insert(sideScan.getPrjCode(), sideScan.getCode(),
                                sideScan.getName(), sideScan.getArea(), sideScan.getTime(),
                                sideScan.getCenterCoordinateX(), sideScan.getCenterCoordinateY(),
                                sideScan.getCenterCoordinateZ(), sideScan.getUrl());
                    }
                }
            }
            return Result.success();

        } catch (IOException e) {
            e.printStackTrace();
            return Result.error("1", e.getMessage());
        }
    }

    /*处理插入WaterDepth类型文件     水深*/
    private Result dealwithWaterDepth(String filePath) {
        try {
            InputStream inputStream = new FileInputStream(new File(filePath));
            Workbook hssfWorkbook = null;
            if (filePath.endsWith("xlsx")) {
                hssfWorkbook = new XSSFWorkbook(inputStream);//Excel 2007
            } else if (filePath.endsWith("xls")) {
                hssfWorkbook = new HSSFWorkbook(inputStream);//Excel 2003
            }
            List<WaterDepth> waterDepths = new ArrayList<WaterDepth>();
            //  循环工作表
            for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
                Sheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
                if (hssfSheet == null) {
                    continue;
                }
                if (numSheet == 0) {
                    // 循环行Row
                    for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                        Row hssRow = hssfSheet.getRow(rowNum);
                        if (hssRow != null) {
                            try {
                                WaterDepth waterDepth = new WaterDepth();
                                Cell PrjCode = hssRow.getCell(0);//项目编码
                                waterDepth.setPrjCode(String.valueOf(PrjCode));
                                Cell Name = hssRow.getCell(1);//项目名称
                                waterDepth.setName(String.valueOf(Name));

                                Cell markpath = hssRow.getCell(2);//标注点存储路径
                                waterDepth.setMarkpath(String.valueOf(markpath));
                                Cell Terrainpath = hssRow.getCell(3);//地形存储路径
                                waterDepth.setTerrainpath(String.valueOf(Terrainpath));

                                if (PrjCode != null && String.valueOf(PrjCode) != ""
                                        && String.valueOf(PrjCode) != "null") {
                                    waterDepths.add(waterDepth);
                                }
                            } catch (Exception e) {
                                logger.error(String.valueOf(e));
                            }
                        }
                    }

                    for (WaterDepth waterDepth : waterDepths) {
                        iWaterDepthService.insert(waterDepth.getPrjCode(), waterDepth.getCode(),
                                waterDepth.getName(), waterDepth.getRegion(), waterDepth.getTime(),
                                waterDepth.getCenterCoordinateX(), waterDepth.getCenterCoordinateY(), waterDepth.getCenterCoordinateZ(),
                                waterDepth.getMarkpath(),waterDepth.getTerrainpath());
                    }
                }
            }
            return Result.success();

        } catch (IOException e) {
            e.printStackTrace();
            return Result.error("1", e.getMessage());
        }
    }


    /**
     * 下载文件模板
     *
     * @param fileType
     * @param response
     * @return
     */
    @RequestMapping("/downExcel/{fileType}")
    public void downloadFile(@PathVariable("fileType") String fileType, HttpServletResponse response) {
        try {
//            Resource resource = new ClassPathResource("excelTemplate/" + fileType + ".xls");
//            File file = resource.getFile();
//            String filename = resource.getFilename();
            File file = new File(MyConfig.getFilepath() + "excelTemplate/" + fileType + ".xls");
            String filename = file.getName();
            InputStream inputStream = new FileInputStream(file);
            //强制下载不打开
            response.setContentType("application/force-download");
            OutputStream out = response.getOutputStream();
            //使用URLEncoder来防止文件名乱码或者读取错误
            response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(filename, "UTF-8"));
            int b = 0;
            byte[] buffer = new byte[1000000];
            while (b != -1) {
                b = inputStream.read(buffer);
                if (b != -1) {
                    out.write(buffer, 0, b);
                }
            }
            inputStream.close();
            out.close();
            out.flush();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}




